﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace MasMembers.DAL
{
    public class EducationEntity
    {
        public Int32 Id { get; set; }
        public Int32 Mem_Id { get; set; }
        public string Edu_Type { get; set; }
        public string Edu_Status { get; set; }
        public string Edu_Geha { get; set; }
        public string Edu_Grade { get; set; }
        public string Edu_Level { get; set; }
        public string Edu_Takhsos { get; set; }

    }

    public class Education
    {
        private static string ConnString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='MasMembers.accdb' ;Jet OLEDB:Database Password=Yarabe11";
        private static string CmdText = "";

        private static EducationEntity FillEntityFromReader(OleDbDataReader reader)
        {
            EducationEntity x = new EducationEntity();
            x.Id = Int32.Parse(reader["Id"].ToString());
            if (reader["Edu_Status"] != null)
                x.Edu_Status = reader["Edu_Status"].ToString();
            if (reader["Mem_Id"] != null)
                x.Mem_Id = Convert.ToInt32(reader["Mem_Id"]);
            if (reader["Edu_Type"] != null)
                x.Edu_Type = reader["Edu_Type"].ToString();
            if (reader["Edu_Geha"] != null)
                x.Edu_Geha = reader["Edu_Geha"].ToString();
            if (reader["Edu_Grade"] != null)
                x.Edu_Grade = reader["Edu_Grade"].ToString();
            if (reader["Edu_Level"] != null)
                x.Edu_Level = reader["Edu_Level"].ToString();
            if (reader["Edu_Takhsos"] != null)
                x.Edu_Takhsos = reader["Edu_Takhsos"].ToString();

            return x;
        }

        public static List<EducationEntity> GetAllEducation()
        {
            EducationEntity x;
            List<EducationEntity> EducationList = new List<EducationEntity>();

            CmdText = "Select * from Education ";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            try
            {
                Con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    x = FillEntityFromReader(reader);
                    EducationList.Add(x);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return EducationList;

        }

        public static List<EducationEntity> GetEducationDataByMemId(Int32 Id)
        {
            EducationEntity x;
            List<EducationEntity> EducationList = new List<EducationEntity>();

            CmdText = "Select * from Education where Mem_Id = ? ";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@Mem_Id", Id);

            try
            {
                Con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    x = FillEntityFromReader(reader);
                    EducationList.Add(x);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return EducationList;
        }

        public static EducationEntity GetFullEducationEntity(EducationEntity P)
        {
            EducationEntity x = null;

            CmdText = "Select * from Education where (Mem_Id = ?  AND  Edu_Type = ?  AND  Edu_Status = ?  AND Edu_Geha = ?  AND  Edu_Grade = ?  AND  Edu_Level = ?  AND  Edu_Takhsos = ? ) ";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@Mem_Id", P.Mem_Id);
            cmd.Parameters.AddWithValue("@Edu_Type", P.Edu_Type);
            cmd.Parameters.AddWithValue("@Edu_Status", P.Edu_Status);
            cmd.Parameters.AddWithValue("@Edu_Geha", P.Edu_Geha);
            cmd.Parameters.AddWithValue("@Edu_Grade", P.Edu_Grade);
            cmd.Parameters.AddWithValue("@Edu_Level", P.Edu_Level);
            cmd.Parameters.AddWithValue("@Edu_Takhsos", P.Edu_Takhsos);

            try
            {
                Con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    x = FillEntityFromReader(reader);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return x;
        }


        public static Int32 Add(EducationEntity P)
        {
            Int32 Id = -1;

            CmdText = "INSERT INTO Education (Mem_Id,Edu_Type,Edu_Status,Edu_Geha,Edu_Grade,Edu_Level,Edu_Takhsos) values (?,?,?,?,?,?,?)";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@Mem_Id", P.Mem_Id);
            cmd.Parameters.AddWithValue("@Edu_Type", P.Edu_Type);
            cmd.Parameters.AddWithValue("@Edu_Status", P.Edu_Status);
            cmd.Parameters.AddWithValue("@Edu_Geha", P.Edu_Geha);
            cmd.Parameters.AddWithValue("@Edu_Grade", P.Edu_Grade);
            cmd.Parameters.AddWithValue("@Edu_Level", P.Edu_Level);
            cmd.Parameters.AddWithValue("@Edu_Takhsos", P.Edu_Takhsos);

            try
            {
                Con.Open();
                Int32 NumOfRaws = cmd.ExecuteNonQuery();
                if (NumOfRaws != 0)
                {
                    Id = 1;
                }
            }
            catch (Exception ex)
            {
                Id = -1;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return Id;
        }

        public static bool Update(EducationEntity P)
        {
            bool flag = true;
            CmdText = "Update Education set Mem_Id = ? , Edu_Type = ? , Edu_Status = ? , Edu_Geha = ? , Edu_Grade = ? , Edu_Level = ? , Edu_Takhsos = ?  Where Id = ?";


            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@Mem_Id", P.Mem_Id);
            cmd.Parameters.AddWithValue("@Edu_Type", P.Edu_Type);
            cmd.Parameters.AddWithValue("@Edu_Status", P.Edu_Status);
            cmd.Parameters.AddWithValue("@Edu_Geha", P.Edu_Geha);
            cmd.Parameters.AddWithValue("@Edu_Grade", P.Edu_Grade);
            cmd.Parameters.AddWithValue("@Edu_Level", P.Edu_Level);
            cmd.Parameters.AddWithValue("@Edu_Takhsos", P.Edu_Takhsos);
            cmd.Parameters.AddWithValue("@Id", P.Id);


            try
            {
                Con.Open();
                Int32 NumOfRaws = cmd.ExecuteNonQuery();
                if (NumOfRaws == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return flag;
        }

        public static bool Delete(int Id)
        {
            bool flag = true;

            CmdText = "delete from Education where Id = ? ";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@Id", Id);


            try
            {
                Con.Open();
                Int32 NumOfRaws = cmd.ExecuteNonQuery();
                if (NumOfRaws == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ فى حذف البيانات" + ex.Message);
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return flag;
        }

    }
}
